package com.gupao.jly521.dbutil;

/**
 * @author Jly
 * @date 2018/8/31  16:43
 */
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by N3verL4nd on 2017/6/8.
 */

class DbUtil {
    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:@172.31.231.187:1521:orcl";
    private static String username = "db_tool";
    private static String password = "db_tool";

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

public class DBUtilsTest {
    public static void testQuery() {
        //创建连接
        Connection conn = DbUtil.getConnection();
        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        List<Person> list = null;
        try {
            //执行SQL查询，并获取结果
            list = queryRunner.query(conn, "select * from persons", new BeanListHandler<>(Person.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (list != null) {
            //输出查询结果
//            list.forEach(System.out::println);
        }
        //关闭数据库连接
        DbUtils.closeQuietly(conn);
    }

    public static void testCreateTable() {
        String sql = "create table TB_STUDENT\n" +
                "\n" +
                "(\n" +
                "\n" +
                " ID        INTEGER,\n" +
                "\n" +
                " NAME      VARCHAR2(256),\n" +
                "\n" +
                " SEX       VARCHAR2(256),\n" +
                "\n" +
                " AGE       VARCHAR2(256),\n" +
                "\n" +
                " GRADE     INTEGER,\n" +
                "\n" +
                " CLASSNAME VARCHAR2(256)\n" +
                "\n" +
                ")";
        //创建连接
        Connection conn = DbUtil.getConnection();
        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        int rows = 0;
        try {
//            conn.setAutoCommit(false);
            rows = queryRunner.update(conn,sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void testUpdate() {
        //创建连接
        Connection conn = DbUtil.getConnection();
        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        int rows = 0;
        try {
            //执行SQL插入
            rows = queryRunner.update(conn, "INSERT INTO persons(name, age) VALUES(?, ?)", "阡陌", 24);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("成功插入" + rows + "条数据！");
        //关闭数据库连接
        DbUtils.closeQuietly(conn);
    }

    public static void main(String[] args) {
//        testUpdate();
//        testQuery();
        testCreateTable();

    }
}
